package org.tsinghua.air;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.tsinghua.air.common.Constant.Database;

public class CreateDB {
	public final static String DRIVER = "org.hsqldb.jdbcDriver";
	public final static String URL = "jdbc:hsqldb:data/db";
//	public final static String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
//	public final static String URL = "jdbc:derby:db;create=true";

	private static Connection conn;

	@BeforeClass
	public static void beforeClass() throws Exception {
		Class.forName(DRIVER);
		conn = DriverManager.getConnection(URL,"sa","");
	}

	@AfterClass
	public static void afterClass() throws Exception {
		conn.close();
	}

	@Test
	public void createTables() throws SQLException {
		Statement st = null;
		try {
			conn.setAutoCommit(false);
			st= conn.createStatement();
			st.execute("create table "
					+ Database.COUNTRY.getTable()
					+ " (country_id int, name varchar(20),longitude varchar(50), latitude varchar(50), area int, PRIMARY KEY(country_id))");
//			st.execute("insert into "
//					+ Database.COUNTRY.getTable()
//					+ " (country_id, NAME, LONGITUDE, LATITUDE, AREA) VALUES (1,'US','north 30~50','west 75~105', 9857000)"); 
//			st.execute("insert into "
//					+ Database.COUNTRY.getTable()
//					+ " (country_id, NAME, LONGITUDE, LATITUDE, AREA) VALUES (2,'China','north 30~50','west 75~105', 9607000)"); 
			
			st.execute("create table " + Database.DEVELOPMENT_STAGE.getTable()
					+ " (country varchar(20), years int, development_stage varchar(50))");
			st.execute("create table "
					+ Database.INDUSTRIALIZATION_INDEX.getTable()
					+ " (country varchar(20), years int, industry_index double)");
			st.execute("create table "
					+ Database.LAW_POLICY.getTable()
					+ " (country varchar(20),years int,sector varchar(50), statute_name varchar(50), target varchar(50),term varchar(50),remarks varchar(50))");
			st.execute("create table "
					+ Database.RULE_OF_LAW_INDEX.getTable()
					+ " (country varchar(20),years int,law_rule_index double)");
			st.execute("create table "
					+ Database.PER_EMISSION.getTable()
					+ " (country varchar(20),years int,per_emission double)");
			st.execute("create table "
					+ Database.PER_GDP.getTable()
					+ " (country varchar(20),years int,gdp_per_capita double)");
			st.execute("create table "
					+ Database.URBAN.getTable()
					+ " (country varchar(20),years int,urban_population double)");

//			st.execute("create table "
//					+ Database.COUNTRY.getTable()
//					+ " (country_id int, name varchar(20),longitude varchar(50), latitude varchar(50), area int, PRIMARY KEY(country_id))");
//			st.execute("insert into "
//					+ Database.COUNTRY.getTable()
//					+ " (COUNTRY_ID, NAME, LONGITUDE, LATITUDE, AREA) VALUES (1,'US','north 30~50','west 75~105', 9857000)"); 
//			
//			st.execute("create table " + Database.DEVELOPMENT_STAGE.getTable()
//					+ " (country_id int, years int, development_stage varchar(50))");
//			st.execute("create table "
//					+ Database.INDUSTRIALIZATION_INDEX.getTable()
//					+ " (country_id int, years int, industry_value_added double, technique varchar(50), structure varchar(50), scale varchar(50))");
//			st.execute("create table "
//					+ Database.LAW_POLICY.getTable()
//					+ " (country_id int,years int,sector varchar(50), statute_name varchar(50), target varchar(50),term varchar(50),remarks varchar(50))");
//			st.execute("create table "
//					+ Database.RULE_OF_LAW_INDEX.getTable()
//					+ " (country_id int,years int,law_rule_index double, constraints_on_government_powers varchar(50), absence_of_corruption varchar(50), open_government varchar(50),"
//					+ "fundamental_rights varchar(50),order_security varchar(50),regulatory_enforcement varchar(50),civil_justice varchar(50),criminal_justice varchar(50),Remarks varchar(50))");
//			st.execute("create table "
//					+ Database.PER_EMISSION.getTable()
//					+ " (country_id int,years int,per_emission double, sector varchar(50), population int,emissions double, remarks varchar(50))");
//			st.execute("create table "
//					+ Database.PER_GDP.getTable()
//					+ " (country_id int,years int,gdp_per_capita double, agriculture_value double, industry_value double,service_industry_value double)");
//			st.execute("create table "
//					+ Database.URBAN.getTable()
//					+ " (country_id int,years int,urban_population double, urban_population_percentage double, energy_consumption double,industry double, transportation double, residents_lives double)");
			
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			if(st!=null) st.close();
			conn.setAutoCommit(true);
		}
	}

	@Test
	public void dropTables() throws SQLException {
		Statement st = null;
		try {
			conn.setAutoCommit(false);
			st =conn.createStatement();
			st.execute("drop table "+Database.COUNTRY.getTable());
			st.execute("drop table "+Database.DEVELOPMENT_STAGE.getTable());
			st.execute("drop table "+Database.INDUSTRIALIZATION_INDEX.getTable());
			st.execute("drop table "+Database.LAW_POLICY.getTable());
			st.execute("drop table "+Database.PER_EMISSION.getTable());
			st.execute("drop table "+Database.PER_GDP.getTable());
			st.execute("drop table "+Database.RULE_OF_LAW_INDEX.getTable());
			st.execute("drop table "+Database.URBAN.getTable());

			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
		} finally {
			if(st!=null) st.close();
			conn.setAutoCommit(true);
		}
	}
}
